﻿
CREATE PROCEDURE [dbo].[GenNotaAL] 
	-- Add the parameters for the stored procedure here
	@IDAL int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;

    BEGIN TRAN

	DELETE FROM [Rulaje]  FROM [AngajamentLegalNC] a
	WHERE [IDAL] = @IDAL AND [Rulaje].[IDDocument] = a.[IDDocument]
	IF @@ERROR <>0  GOTO EroareStergere

	DELETE FROM [RulajeDoc]  FROM [AngajamentLegalNC] a
	WHERE [IDAL] = @IDAL AND [RulajeDoc].[IDDocument] = a.[IDDocument]
	IF @@ERROR <>0  GOTO EroareStergere

	DELETE FROM [AngajamentLegalNC] 
	WHERE [IDAL] = @IDAL
	IF @@ERROR <>0  GOTO EroareStergere


	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost],[CodUnitate])   
	SELECT
	[SumaTotala] AS ValoareLei,
	0 AS ValoareValuta,
	Numar AS NumarDoc,
	Data AS DataDoc,
	'ALG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) AS NumarNota,
	3 AS TipFormula, --monopartida
	IdJurnal,
	'Nota Contabila pentru AL nr. ' + CAST(Numar AS varchar(20)) AS Observatii,
	system_user AS xUser
	,host_name() AS xHost,
	CodUnitate
	FROM [AngajamentLegal] a JOIN 
	(SELECT DISTINCT IDJurnal FROM[AngajamentLegalDetaliu] b WHERE b.[IDAL]=@IDAL) b ON  a.[IDAL]=@IDAL 
	IF @@ERROR <>0  GOTO EroareDoc

	INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt],[CodUnitate])
	SELECT 
	CASE WHEN [Suma] > 0 THEN ebs.[ContAB]+'.' + abd.[ArtAl] ELSE 'N/A' END ContDebit,
	CASE WHEN [Suma] < 0 THEN 'N/A' ELSE ebs.[ContAB]+'.' + abd.[ArtAl] END ContCredit,
	0 AS CodValuta,
	rd.[IDDocument] AS IDDocument,
	0 AS ValoareValuta,
	0 AS Curs,
	Suma,
	ab.[Data] AS DataDocJust,
	'AL' AS TipDocJust,
	ab.[Numar] AS NumarDocJust,
	'Angajament Legal'  AS ExplicatiiDocJust,
	0 AS NrCrt,
	ab.[CodUnitate]
	FROM [AngajamentLegalDetaliu] abd JOIN
	[AngajamentLegal] ab ON ab.[IDAL] = abd.[IDAL] JOIN
	[ExBugSetup] ebs ON ebs.[CodUnitate] = ab.[CodUnitate] JOIN
	[RulajeDoc] rd ON 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= rd.[NumarNota]
	WHERE ab.[IDAL]=@IDAL		
	IF @@ERROR <>0  GOTO EroareRul

	UPDATE [RulajeDoc] SET [ValoareLei] = (SELECT SUM([ValoareLei]) FROM [Rulaje] r WHERE r.[IDDocument] = [RulajeDoc].[IDDocument])	
	FROM [AngajamentLegal] ab 
	WHERE ab.[IDAL] = @IDAL AND 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= [NumarNota]
	IF @@ERROR <>0  GOTO EroareDocSuma

	INSERT INTO [AngajamentLegalNC] ([IDAL],[IDDocument])
	SELECT [IDAL],[IDDocument] FROM [AngajamentLegal] ab JOIN [RulajeDoc] rd
	ON ab.[IDAL] = @IDAL AND 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= rd.[NumarNota]
	IF @@ERROR <>0  GOTO EroareAB

	COMMIT TRAN
	GOTO Gata
	EroareDoc:
		ROLLBACK
		RAISERROR('Eroare la generarea documentului ',16,-1)
		return
	EroareAB:
		ROLLBACK
		RAISERROR('Eroare la generarea legaturilor intre documente ',16,-1)
		return
	EroareDocSuma:
		ROLLBACK
		RAISERROR('Eroare la actualizarea sumei pe nota contabila ',16,-1)
		return
	EroareRul:
		ROLLBACK
		RAISERROR('Eroare la generarea rulajului ',16,-1)
		return
	EroareStergere:
		ROLLBACK
		RAISERROR('Eroare la stergerea notei anterioare ',16,-1)
		return

	Gata:
END